package com.sf.dlb.mapper.dao;

import com.sf.dlb.jpa.model.UserGradeBuyRecordInfo;
import com.sf.dlb.jpa.model.UserInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mapstruct.Mapper;

import java.util.List;
import java.util.Map;

/**
 * Created by tanjing on 2019/5/10.
 */
@Mapper
public interface UserGradeMapper {
    /**
     * 获取用户累计购买等级的金额,等级未过期
     * @return
     */
    @Select("SELECT u.user_id,u.user_grade,sum(r.pay_price) AS 'moneyPoolCount' FROM user_info u INNER JOIN user_grade_buy_record r ON u.user_id=r.user_id AND u.user_grade!='0' AND u.user_grade!='' AND u.user_grade IS NOT NULL GROUP BY u.user_id")
    List<Map<String,Object>> getAllUserMoneyPoolCount();
    /**
     * 获取从来没有购买过等级的用户
     * @return
     */
    @Select("SELECT a.user_id,a.create_date FROM user_info a LEFT JOIN user_grade_buy_record r on a.user_id=r.user_id WHERE a.user_grade='0' AND r.user_id IS NULL AND a.del_flag='0'")
    List<UserInfo> getNotBuyGradeUser();
    /**
     * 获取购买过等级，过期了的用户
     *  购买记录userId分组，排序
     * @return
     */
    @Select("SELECT r.user_id,GROUP_CONCAT(r.validity_time ORDER BY r.validity_time DESC) AS 'validity_time' FROM user_info a RIGHT JOIN  user_grade_buy_record r ON a.user_id=r.user_id WHERE a.user_grade='0' AND a.del_flag='0' GROUP BY r.user_id")
    List<UserGradeBuyRecordInfo> getBuyGradeAndOverdueUser();
    /**
     * 获取购买等级总奖金池
     * @return
     */
    @Select("SELECT SUM(cast(a.pay_price AS DECIMAL(18,3))) FROM user_grade_buy_record a")
    Double getBuyGradeMoneyPoolCount();

    /**
     * 查询用户购买分成有没有上限
     * @return
     */
    @Select("SELECT i.invitation_user_id FROM (SELECT * FROM user_invitation u WHERE u.user_id=#{userId}) i  LEFT JOIN  user_grade_buy_record a ON a.user_id=i.invitation_user_id WHERE (a.user_id IS NOT NULL or a.user_id='') GROUP BY a.user_id ORDER BY a.create_date LIMIT 0,#{limit}")
    List<String> queryUserBuy(@Param("userId") String userId,@Param("limit")int limit);
}
